package com.zzz.web.DB;


import com.zzz.web.Data.NewRssi;
import com.zzz.web.Data.RSSI;
import com.zzz.web.Data.XYaxis;
import org.junit.Test;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class operationOnDataBase extends linkDB {

    //向数据库中插入数据
    public boolean insertIntoData(RSSI rssi) {

        conn = getConnection();
        PreparedStatement pst = null;
        String sql = "REPLACE INTO the_data_of_finger_point"
                + "(number,frequency,MACAP1,RSSIAP1,MACAP2,RSSIAP2,MACAP3,RSSIAP3)"
                + "VALUES (?,?,?,?,?,?,?,?)";

        try {
            pst = conn.prepareStatement(sql);

            pst.setLong(1, rssi.getNumber());
            pst.setLong(2, rssi.getFrequency());
            pst.setString(3, rssi.getAP1MAC());
            pst.setLong(4, rssi.getAP1RSSI());
            pst.setString(5, rssi.getAP2MAC());
            pst.setLong(6, rssi.getAP2RSSI());
            pst.setString(7, rssi.getAP3MAC());
            pst.setLong(8, rssi.getAP3RSSI());

            pst.executeUpdate();
            System.out.println("插入成功");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pst != null)
                    pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            closeConnection();
        }

        return true;
    }


    public boolean addNewRssi(NewRssi newRssi) {

        conn = getConnection();
        PreparedStatement pst = null;
        String sql = "REPLACE INTO newrssi VALUES (?,?,?,?,?,?,?,?) ";

        try {
            pst = conn.prepareStatement(sql);

            pst.setFloat(1,newRssi.getX());
            pst.setFloat(2,newRssi.getY());

            pst.setString(3,newRssi.getAp1Mac());
            pst.setInt(4,newRssi.getAp1Rssi());

            pst.setString(5,newRssi.getAp2Mac());
            pst.setInt(6,newRssi.getAp2Rssi());

            pst.setString(7,newRssi.getAp3Mac());
            pst.setInt(8,newRssi.getAp3Rssi());

            pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pst != null)
                    pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            closeConnection();
        }

        return true;
    }

    //频率加一
    public boolean updateFrequency(int number) throws SQLException {
        int lastCurrentFrequency = 0;

        String selsectSql = "select frequency from the_data_of_finger_point where number=" + number;
        conn = getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(selsectSql);
        while (rs.next()) {
            lastCurrentFrequency = rs.getInt("frequency");
        }
        lastCurrentFrequency += 1;
        String updatesql = "update the_data_of_finger_point "
                + "set frequency=" + lastCurrentFrequency
                + " where number="+number;

        statement.executeUpdate(updatesql);

        rs.close();
        statement.close();
        closeConnection();
        return true;
    }


    //查询数据库中现有的数据
    public List<RSSI> selectRssiDataFromDB() throws Exception {
        List<RSSI> rssiList = new ArrayList<>();

        String sql = "select * from the_data_of_finger_point";
        conn = getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            RSSI temprssi = new RSSI();
            temprssi.setNumber(rs.getInt("number"));
            temprssi.setAP1MAC(rs.getString("MACAP1"));
            temprssi.setAP2MAC(rs.getString("MACAP2"));
            temprssi.setAP3MAC(rs.getString("MACAP3"));
            temprssi.setAP1RSSI(rs.getInt("RSSIAP1"));
            temprssi.setAP2RSSI(rs.getInt("RSSIAP2"));
            temprssi.setAP3RSSI(rs.getInt("RSSIAP3"));
            rssiList.add(temprssi);
        }

        rs.close();
        statement.close();
        closeConnection();

        return rssiList;
    }

    //向XY 表中插入数据
    public boolean insertIntoXY(XYaxis xy) {
        conn = getConnection();
        PreparedStatement pst = null;

        String sql = "insert into xy(x,y) values(?,?)";

        try {
            pst = conn.prepareStatement(sql);
            pst.setDouble(1, xy.getX());
            pst.setDouble(2, xy.getY());
            pst.executeUpdate();
//            System.out.println("插入成功");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pst != null)
                    pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            closeConnection();
        }
        return true;
    }


    //向XY 表中插入数据
    public boolean insertIntoXY2(XYaxis xy) {
        conn = getConnection();
        PreparedStatement pst = null;

        String sql = "insert into xy_2 values(?,?)";

        try {
            pst = conn.prepareStatement(sql);
            pst.setDouble(1, xy.getX());
            pst.setDouble(2, xy.getY());
            pst.executeUpdate();
//            System.out.println("插入成功");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pst != null)
                    pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            closeConnection();
        }
        return true;
    }


    public List<XYaxis> selectXYFromDB() throws Exception {
        List<XYaxis> xYaxisList = new ArrayList<>();

        String sql = "select * from xy";
        conn = getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            XYaxis tempxy = new XYaxis();
            tempxy.setX(rs.getDouble("x"));
            tempxy.setY(rs.getDouble("y"));

            xYaxisList.add(tempxy);
        }
        rs.close();
        statement.close();
        closeConnection();
        return xYaxisList;
    }


    public List<XYaxis> selectXYFromDBSecond() throws Exception {
        List<XYaxis> xYaxisList = new ArrayList<>();

        String sql = "select * from xy_2";
        conn = getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            XYaxis tempxy = new XYaxis();
            tempxy.setX(rs.getDouble("x"));
            tempxy.setY(rs.getDouble("y"));

            xYaxisList.add(tempxy);
        }
        rs.close();
        statement.close();
        closeConnection();
        return xYaxisList;
    }

    public List<NewRssi> allNewRssi() throws Exception {
        List<NewRssi> rssiList = new ArrayList<>();

        String sql = "select * from newrssi";
        conn = getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            NewRssi newRssi = new NewRssi();
            newRssi.setX(rs.getFloat("x"));
            newRssi.setY(rs.getFloat("y"));
            newRssi.setAp1Mac(rs.getString("ap1mac"));
            newRssi.setAp1Rssi(rs.getInt("ap1rssi"));
            newRssi.setAp2Mac(rs.getString("ap2mac"));
            newRssi.setAp2Rssi(rs.getInt("ap2rssi"));
            newRssi.setAp3Mac(rs.getString("ap3mac"));
            newRssi.setAp3Rssi(rs.getInt("ap3rssi"));

            rssiList.add(newRssi);
        }
        rs.close();
        statement.close();
        closeConnection();
        return rssiList;
    }

}
